﻿using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using ExcelReader.Data;
using DocumentFormat.OpenXml.Packaging;
using System.Text.RegularExpressions;

namespace ExcelReader.Helper
{
    internal static class ExcelHelper
    {
        internal static List<MergeCellInfo> MergeCellInformation(WorksheetPart dataSheetPart)
        {
            var mergeCellInformation = new List<MergeCellInfo>();

            if (dataSheetPart.Worksheet.Elements<MergeCells>().Any())
            {
                List<MergeCells> mergeCells = dataSheetPart.Worksheet.Elements<MergeCells>().ToList();

                var cells = mergeCells[0].ToArray();

                var maxCount = cells.Count();
                int counter = 0;
                if (maxCount > 0)
                {
                    while (counter < maxCount)
                    {
                        var cell = (MergeCell)cells[counter];
                        string reference = cell.Reference;
                        var temp = reference.Split(':');

                        var mergeCellInfo = new MergeCellInfo();
                        mergeCellInfo.Id = Guid.NewGuid();
                        mergeCellInfo.StartColumn = Regex.Replace(temp[0], @"\d", "");
                        mergeCellInfo.LastColumn = Regex.Replace(temp[1], @"\d", "");
                        mergeCellInfo.StartIndex = int.Parse(Regex.Replace(temp[0], "[^.0-9]", ""));
                        mergeCellInfo.LastIndex = int.Parse(Regex.Replace(temp[1], "[^.0-9]", ""));
                        mergeCellInformation.Add(mergeCellInfo);
                        counter++;
                    }
                }
            }
            return mergeCellInformation;
        }

        internal static string OverideExcelCoordinate(List<MergeCellInfo> mergeMasterList, string cellCurrentCellReference)
        {
            string columnName = Regex.Replace(cellCurrentCellReference, @"\d", "");
            int columnIndex = int.Parse(Regex.Replace(cellCurrentCellReference, "[^.0-9]", ""));

            var matchMergeInfo = mergeMasterList.FirstOrDefault(p => p.StartColumn.Equals(columnName) && (columnIndex >= p.StartIndex && columnIndex <= p.LastIndex));
            if (matchMergeInfo == null)
                return cellCurrentCellReference;
            return string.Format("{0}{1}", matchMergeInfo.StartColumn, matchMergeInfo.StartIndex);
        }

        internal static string GetCellValue(string addressName, WorkbookPart wbPart, WorksheetPart wsPart)
        {
            string value = null;

            // Use its Worksheet property to get a reference to the cell 
            // whose address matches the address you supplied.
            var cell = wsPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == addressName);

            // If the cell does not exist, return an empty string.
            if (cell != null)
            {
                //Faruq : This to check if we detect there's formula in the cell, then we need to get the cell value.
                value = cell.CellFormula != null ? cell.CellValue.Text : cell.InnerText;

                // If the cell represents an integer number, you are done. 
                // For dates, this code returns the serialized value that 
                // represents the date. The code handles strings and 
                // Booleans individually. For shared strings, the code 
                // looks up the corresponding value in the shared string 
                // table. For Booleans, the code converts the value into 
                // the words TRUE or FALSE.
                if (cell.DataType != null)
                {
                    switch (cell.DataType.Value)
                    {
                        case CellValues.SharedString:

                            // For shared strings, look up the value in the
                            // shared strings table.
                            var stringTable =
                                wbPart.GetPartsOfType<SharedStringTablePart>()
                                .FirstOrDefault();

                            // If the shared string table is missing, something 
                            // is wrong. Return the index that is in
                            // the cell. Otherwise, look up the correct text in 
                            // the table.
                            if (stringTable != null)
                            {
                                value =
                                    stringTable.SharedStringTable
                                    .ElementAt(int.Parse(value)).InnerText;
                            }
                            break;

                        case CellValues.Boolean:
                            switch (value)
                            {
                                case "0":
                                    value = "FALSE";
                                    break;
                                default:
                                    value = "TRUE";
                                    break;
                            }
                            break;

                        case CellValues.Date:
                            int i = 0;
                            break;

                    }
                }
            }
            return value;
        }
    }
}
